﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;

namespace Backend
{
    /* Order Class
     * This class will handle all orders and 
     * the methods to create, search and update them. */
    class Order
    {
		DBConnect dbconn = new DBConnect();
        Employee employee = new Employee();
        Customer customer = new Customer();
        Scooter scooter = new Scooter();

        public bool Check(int oid)
        {
            int results;
            string query;

            query = dbconn.RunQueryReturn("SELECT COUNT(*) FROM `order` WHERE oid = '" + oid + "'");
            results = Convert.ToInt32(query);

            if (results > 0)
                return true;
            else
                return false;
        }

        public void Create(int cid, int eid, string date, out bool result, out int orderID)
        {
            if (customer.Check(cid) != true)
            {
                result = false;
                orderID = 0;
            }
            else
            {
                string[] insertSet = new string[4];

                insertSet[0] = cid.ToString(); // Customer ID
                insertSet[1] = eid.ToString(); // Employee ID
                insertSet[2] = date; // Date for creation
                insertSet[3] = "no"; // Discount for order

                dbconn.RunQuery("INSERT INTO `order` (cid, eid, date, smsnotification) VALUES ('" + string.Join("', '", insertSet) + "')");

                orderID = Convert.ToInt32(dbconn.RunQueryReturn("SELECT MAX(oid) FROM `order`"));
                result = true;
            }
        }

        public bool Update(int oid, int eid, string smsnotification, string status)
        {
            if (Check(oid) != true)
                return false;
            else
            {
                string[] updateSet = new string[3];

                updateSet[0] = "eid = '" + eid + "'";
                updateSet[1] = "smsnotification = '" + smsnotification + "'";
                updateSet[2] = "status = '" + status + "'";

                dbconn.RunQuery("UPDATE `order` SET " + string.Join(", ", updateSet) + " WHERE oid = '" + oid + "' LIMIT 1");

                return true;
            }
        }

        public DataTable Find(string telephone = "", string fullName = "", int mechanic = 0)
        {
            DataTable orderTable;

            if (fullName != "")
                orderTable = dbconn.BetterDataTable("SELECT o.oid, o.date, o.cid, o.eid, o.totalprice FROM `order` AS o, customer AS c WHERE c.fullname LIKE '%" + fullName + "%' AND c.cid = o.cid", 5);
            else if (telephone != "")
                orderTable = dbconn.BetterDataTable("SELECT o.oid, o.date, o.cid, o.eid, o.totalprice FROM `order` AS o, customer AS c WHERE c.telephone LIKE '%" + telephone + "%' AND c.cid = o.cid", 5);
            else if (mechanic != 0)
                orderTable = dbconn.BetterDataTable("SELECT o.oid, o.date, o.cid, o.eid, o.totalprice FROM `order` AS o, employee AS e WHERE o.eid = e.eid AND o.eid = '" + mechanic + "'", 5);
            else
                orderTable = dbconn.BetterDataTable("SELECT oid, date, cid, eid, totalprice FROM `order`", 5);

            
            return orderTable;
        }

        public void AddProduct(int oid, int pid, string productPrice, int quantity, out bool result, out int olid)
        {
            if (Check(oid) != true)
            {
                olid = 0;
                result = false;
            }
            else
            {
                Product product = new Product();

                if (product.Check(pid) != true)
                {
                    olid = 0;
                    result = false;
                }
                else
                {
                    double price = Convert.ToDouble(productPrice) * quantity;
                    string subtotal = Convert.ToString(price).Replace(',', '.');

                    dbconn.RunQuery("INSERT INTO orderline (oid, pid, quantity, subtotal) VALUES ('" + oid + "', '" + pid + "', '" + quantity + "', '" + subtotal + "')");
                    dbconn.RunQuery("UPDATE `order` SET totalprice = totalprice + " + subtotal + " WHERE oid = '" + oid + "'");

                    olid = Convert.ToInt32(dbconn.RunQueryReturn("SELECT MAX(olid) FROM orderline"));
                    result = true;
                }
            }
        }

        public bool RemoveProduct(int oid, int olid, string totalprice)
        {
            if (Check(oid) != true)
                return false;
            else
            {
                dbconn.RunQuery("DELETE FROM orderline WHERE olid = '" + olid +"'");
                dbconn.RunQuery("UPDATE `order` SET totalprice = '" + totalprice + "' WHERE oid = '" + oid + "'");
                return true;
            }
        }

        public DataTable Scooters(int oid)
        {
            DataTable scooterTable = dbconn.BetterDataTable("SELECT s.bid, s.vin, s.registration FROM scooter AS s, `order` AS o, orderscooter AS os WHERE o.oid = '" + oid + "' AND o.oid = os.oid AND os.vin = s.vin", 3);
            return scooterTable;
        }

        public bool AddScooter(int oid, string vin)
        {
            if (Check(oid) != true)
                return false;
            else
            {
                if (scooter.Check(vin) != true)
                    return false;
                else
                {
                    dbconn.RunQuery("INSERT INTO orderscooter VALUES ('" + vin + "', '" + oid + "')");
                    return true;
                }
            }
        }

        public bool RemoveScooter(int oid, string vin)
        {
            if (Check(oid) != true)
                return false;
            else
            {
                if (scooter.Check(vin) != true)
                    return false;
                else
                {
                    dbconn.RunQuery("DELETE FROM orderscooter WHERE oid = '" + oid + "' AND vin = '" + vin + "'");
                    return true;
                }
            }
        }

        public DataTable List(int cid)
        {
            DataTable orderTable = dbconn.BetterDataTable("SELECT oid, eid, date, totalprice, status FROM `order` WHERE cid = '" + cid + "'", 5);
            return orderTable;
        }

        public string[] Last(string cid)
        {
            string[] result = dbconn.ReturnArray("SELECT MAX(oid), date FROM `order` WHERE cid = '" + cid + "'", 2);
            return result;
        }

        public string[] GetInfo(int oid)
        {
            string[] orderInfo = dbconn.ReturnArray("SELECT cid, totalprice, eid, status, smsnotification FROM `order` WHERE oid = '" + oid + "'", 5);

            return orderInfo;
        }

        public DataTable GetOrderlines(int oid)
        {
            DataTable orderlinesTable = dbconn.BetterDataTable("SELECT ol.olid, ol.quantity, ol.subtotal, p.name, p.price FROM orderline as ol, `order` AS o, product AS p WHERE ol.oid = o.oid AND ol.pid = p.pid AND o.oid = '" + oid + "'", 5);
            return orderlinesTable;
        }

        public string[] GetOrderlineInfo(int olid)
        {
            string[] orderlineInfo = dbconn.ReturnArray("SELECT oid, pid, quantity, subtotal FROM orderline WHERE olid = '" + olid + "'", 4);

            return orderlineInfo;
           }

	}
	
    

    /* Invoice Class
     * This class will handle all the finished orders
     * which is ready to be invoiced. It will also contain
     * all the methods to print or search invoices. */
    class Invoice
    {
        DBConnect dbconn = new DBConnect();
        Order order = new Order();
        Customer customer = new Customer();
        Employee employee = new Employee();

        public DataTable Find(string telephone = "", string fullName = "", int mechanic = 0)
        {
            DataTable orderTable;

            if (fullName != "")
                orderTable = dbconn.BetterDataTable("SELECT o.oid, o.date, o.cid, o.eid, o.totalprice FROM `order` AS o, customer AS c WHERE c.fullname LIKE '%" + fullName + "%' AND c.cid = o.cid AND o.status = 'completed'", 5);
            else if (telephone != "")
                orderTable = dbconn.BetterDataTable("SELECT o.oid, o.date, o.cid, o.eid, o.totalprice FROM `order` AS o, customer AS c WHERE c.telephone LIKE '%" + telephone + "%' AND c.cid = o.cid AND o.status = 'completed'", 5);
            else if (mechanic != 0)
                orderTable = dbconn.BetterDataTable("SELECT o.oid, o.date, o.cid, o.eid, o.totalprice FROM `order` AS o, employee AS e WHERE o.eid = e.eid AND o.eid = '" + mechanic + "' AND o.status = 'completed'", 5);
            else
                orderTable = dbconn.BetterDataTable("SELECT oid, date, cid, eid, totalprice FROM `order` WHERE status = 'completed'", 5);


            return orderTable;
        }

        public string Get(int orderID)
        {
            string invoice;

            string[] orderArray = order.GetInfo(orderID);
            string[] customerArray = customer.GetInfo(Convert.ToInt32(orderArray[0]));
            string[] employeeArray = employee.GetInfo(Convert.ToInt32(orderArray[2]));

            invoice = "FAKTURA\n\nScooterLand\nHestedamsgade 11\n8700 Horsens\nTlf.: 41828312\nCVR: 25348745\n\nOrdre nummer: " + orderID + "\nMekaniker: " + employeeArray[1] +
                   "\n\n" + customerArray[1] + "\n" + customerArray[2] + "\n" + customerArray[3] + " " + customerArray[6] + "\nTlf.: " + customerArray[4] +
                   "\n\nVarebeskrivelse\t\t\t\t\t\tPris\n---------------------------------------------------------------\n\n";

            DataTable orderlines = order.GetOrderlines(orderID);
            for (int i = 0; i < orderlines.Rows.Count; i++)
            {
                invoice += orderlines.Rows[i][3] + "\n x " + orderlines.Rows[i][1] + "\t\t\t\t\t\t\t" + orderlines.Rows[i][2] + "\n\n";
            }

            invoice += "---------------------------------------------------------------\n\t\t\t\t\tTotalt: " + orderArray[1] + " DKK\n";

            return invoice;
        }

        public string Print(int orderID, string[] invoice)
        {
            string result = orderID + ".txt";

            StreamWriter outputFile;
            outputFile = File.CreateText("Faktura/" + orderID + ".txt");
            
            for (int i = 0; i < invoice.Length; i++)
                outputFile.WriteLine(invoice[i]);

            outputFile.Close();

            return result;
        }
    }

}